package godb

import (
	"database/sql"
	"errors"
	"fmt" //引入mysql 驱动类 切记，切记，切记
	_ "github.com/go-sql-driver/mysql"
	"time"
	"gitee.com/zhgk/tool"
)

var (
	ErrDbNilHost    = errors.New("数据库：连接地址不能为空")
	ErrDbNilUser    = errors.New("数据库：用户名不能为空")
	ErrDbNilPass    = errors.New("数据库：用户密码不能为空")
	ErrDbNilSchema  = errors.New("数据库：实例不能为空")
	ErrDbStartTran  = errors.New("数据库：开启事务失败")
	ErrDbPrepareSql = errors.New("数据库：预编译 SQL失败")
	ErrDbExecSql    = errors.New("数据库：执行语句出错")
	ErrDbCommit     = errors.New("数据库：提交事务失败")
	ErrDbRollBack   = errors.New("数据库：回滚失败")
	ErrDbEmptySQL   = errors.New("数据库：执行SQL不能为空")
)

//数据库句柄
var Orm *sql.DB

// connFormatURL Mysql连接地址模板
var connFormatURL = "%s:%s@tcp(%s:%d)/%s?charset=utf8&parseTime=True&loc=Local"

//根据传入的参数构建连接地址
func buildConnURL(dbMap map[interface{}]interface{}) (string, error) {
	if _, ok := dbMap["HOST"]; !ok {
		return "", ErrDbNilHost
	}
	if _, ok := dbMap["PORT"]; !ok {
		dbMap["PORT"] = 3306
	}
	if _, ok := dbMap["USER"]; !ok {
		return "", ErrDbNilUser
	}
	if _, ok := dbMap["PASS"]; !ok {
		return "", ErrDbNilPass
	}
	if _, ok := dbMap["SCHEMA"]; !ok {
		return "", ErrDbNilSchema
	}

	return fmt.Sprintf(connFormatURL, dbMap["USER"], dbMap["PASS"], dbMap["HOST"], dbMap["PORT"], dbMap["SCHEMA"]), nil

}

// 注册数据库
func RegisterDB(dbMap map[interface{}]interface{}) error {
	connURL, err := buildConnURL(dbMap)
	if err != nil {
		return err
	}
	fmt.Println(connURL)
	Orm, err = sql.Open("mysql", connURL)
	if err != nil {
		return err
	}
	maxOpen := 20
	if v, ok := dbMap["MAX_CON"]; ok {
		maxOpen = v.(int)
	}
	maxIdle := 5
	if v, ok := dbMap["MAX_IDLE"]; ok {
		maxIdle = v.(int)
	}
	Orm.SetMaxOpenConns(maxOpen)
	Orm.SetMaxIdleConns(maxIdle)
	Orm.SetConnMaxLifetime(3600 * time.Second)
	return Orm.Ping()
}

// Exec Exec 执行SQL
func Exec(sqlStr string, args ...interface{}) (int64, error) {
	if sqlStr == "" {
		return -1, ErrDbEmptySQL
	}

	//执行SQL语言
	result, err := Orm.Exec(sqlStr, args...)
	if err != nil {
		return -1, err
	}
	count, err := result.RowsAffected()
	return count, err
}

// ExecTx 执行事务SQL
func ExecTx(sqlStr string, args ...interface{}) (int64, error) {
	if sqlStr == "" {
		return -1, ErrDbEmptySQL
	}
	tx, err := Orm.Begin()
	if err != nil {
		return -1, err
	}
	defer tx.Commit()
	stmt, err := tx.Prepare(sqlStr)
	if err != nil {
		fmt.Println(err.Error())
		tx.Rollback()
		return -1, err
	}
	defer stmt.Close()
	result, err := stmt.Exec(args...)
	if err != nil {
		fmt.Println(err.Error())
		tx.Rollback()
		return -1, err
	}
	return result.RowsAffected()
}

// ExecTx 执行SQL
func ExecPrepare(sqlStr string, args ...interface{}) (int64, error) {
	if sqlStr == "" {
		return -1, ErrDbEmptySQL
	}
	stmt, err := Orm.Prepare(sqlStr)
	if err != nil {
		return -1, ErrDbPrepareSql
	}
	defer stmt.Close()
	result, err := stmt.Exec(args...)
	if err != nil {
		return -1, err
	}
	return result.RowsAffected()
}

//BeginTx 开启事务
func BeginTx() (*sql.Tx, error) {
	return Orm.Begin()
}

// TxExec 事务执行语句
// tx,err := SQLTool.BeginTx()
func TxExec(tx *sql.Tx, sqlStr string, args ...interface{}) (int64, error) {
	if sqlStr == "" {
		return -1, ErrDbEmptySQL
	}
	stmt, err := tx.Prepare(sqlStr)
	if err != nil {
		fmt.Println(err.Error())
		tx.Rollback()
		return -1, err
	}
	defer stmt.Close()

	result, err := stmt.Exec(args...)
	if err != nil {
		fmt.Println(err.Error())
		tx.Rollback()
		return -1, err
	}
	return result.RowsAffected()
}

// FindOne 查询单条记录
// 结果集转驼蜂
func FindOne(queryStr string, args ...interface{}) (record Record, err error) {
	if queryStr == "" {
		return nil, ErrDbEmptySQL
	}

	rows, err := Orm.Query(queryStr+" limit 1 ", args...)
	//延时关闭Rows
	//调用go-sql-server\Mysql驱动查询
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	//获取记录列
	columns, err := rows.Columns()
	if err != nil {
		return nil, err
	}
	//拼接记录Map
	values := make([]sql.RawBytes, len(columns))
	scans := make([]interface{}, len(columns))
	for i := range values {
		scans[i] = &values[i]
	}
	for rows.Next() {
		_ = rows.Scan(scans...)
		each := make(Record)
		for i, col := range values {
			each[tool.UnderToCam(columns[i])] = string(col)
		}
		record = each
		break
		//仅读取第一条记录
	}
	return record, nil
}

// FindMany 查询多条记录
// 列名转为驼峰
func FindMany(queryStr string, args ...interface{}) (records Records, err error) {
	if queryStr == "" {
		return nil, ErrDbEmptySQL
	}

	rows, err := Orm.Query(queryStr, args...)

	//调用go-sql-server\Mysql驱动查询
	if err != nil {
		return nil, err
	}
	//延时关闭Rows
	defer rows.Close()
	//获取记录列
	columns, err := rows.Columns()
	if err != nil {
		return nil, err
	}
	//拼接记录Map
	values := make([]sql.RawBytes, len(columns))
	scans := make([]interface{}, len(columns))
	for i := range values {
		scans[i] = &values[i]
	}
	//转换列名
	for i := 0; i < len(columns); i++ {
		//列名转驼蜂
		columns[i] = tool.UnderToCam(columns[i])
	}
	for rows.Next() {
		_ = rows.Scan(scans...)
		each := make(Record)
		for i, col := range values {
			each[tool.UnderToCam(columns[i])] = string(col)
		}
		records = append(records, each)
	}
	return records, nil
}

// FindOneRaw 查询单条记录，返回原生结果
func FindOneRaw(queryStr string, args ...interface{}) (record Record, err error) {
	if "" == queryStr {
		return nil, ErrDbEmptySQL
	}

	rows, err := Orm.Query(queryStr+" limit 1 ", args...)

	//调用go-sql-server\Mysql驱动查询
	if err != nil {
		return nil, err
	}
	//延时关闭Rows
	defer rows.Close()
	//获取记录列
	columns, err := rows.Columns()
	if err != nil {
		return nil, err
	}
	//拼接记录Map
	values := make([]sql.RawBytes, len(columns))
	scans := make([]interface{}, len(columns))
	for i := range values {
		scans[i] = &values[i]
	}
	//转换列名
	for i := 0; i < len(columns); i++ {
		//列名转驼蜂
		columns[i] = tool.UnderToCam(columns[i])
	}
	for rows.Next() {
		_ = rows.Scan(scans...)
		each := make(Record)
		for i, col := range values {
			each[columns[i]] = string(col)
		}
		record = each
		break
		//仅读取第一条记录
	}
	return record, nil
}

// FindManyRaw 查询多条记录,返回原生结果集
func FindManyRaw(queryStr string, args ...interface{}) (records Records, err error) {
	if "" == queryStr {
		return nil, ErrDbEmptySQL
	}

	rows, err := Orm.Query(queryStr, args...)

	//调用go-sql-server\Mysql驱动查询
	if err != nil {
		return nil, err
	}
	//延时关闭Rows
	defer rows.Close()
	//获取记录列
	columns, err := rows.Columns()
	if err != nil {
		return nil, err
	}
	//拼接记录Map
	values := make([]sql.RawBytes, len(columns))
	scans := make([]interface{}, len(columns))
	for i := range values {
		scans[i] = &values[i]
	}
	for rows.Next() {
		_ = rows.Scan(scans...)
		each := make(Record)
		for i, col := range values {
			each[columns[i]] = string(col)
		}
		records = append(records, each)
	}
	return records, nil
}

// findPage 分页查询
// 结果集转驼蜂
func FindPage(queryStr string, start int, size int, args ...interface{}) (page PageRecord, err error) {
	if "" == queryStr {
		return PageRecord{}, ErrDbEmptySQL
	}
	//统计记录条数
	countSql := " select count(1) as count from (" + queryStr + ") pageTemp "
	record, err := FindOne(countSql, args)
	totalCount := record.GetValue("count")
	//统计数据
	queryStr = queryStr + fmt.Sprintf("limt %d,%d", start, size)
	dataList, err := FindMany(queryStr, args...)
	//返回分页
	page = PageRecord{}
	page.total = totalCount.(int64)
	page.data = dataList
	return page, err
}

// findPageRaw 分页查询，返回原生分页
func FindPageRaw(queryStr string, start int, size int, args ...interface{}) (page PageRecord, err error) {
	if "" == queryStr {
		return PageRecord{}, ErrDbEmptySQL
	}
	//统计记录条数
	countSql := " select count(1) as count from (" + queryStr + ") pageTemp "
	record, err := FindOneRaw(countSql, args)
	totalCount := record.GetValue("count")
	//统计数据
	queryStr = queryStr + fmt.Sprintf("limt %d,%d", start, size)
	dataList, err := FindManyRaw(queryStr, args...)
	//返回分页
	page = PageRecord{}
	page.total = totalCount.(int64)
	page.data = dataList
	return page, err
}

//批量插入多条记录
func InsertMany(sqlStr string, params [][]interface{}) (int64, error) {
	if "" == sqlStr {
		return -1, ErrDbEmptySQL
	}
	tx, err := Orm.Begin()

	if err != nil {
		return -1, err
	}
	defer tx.Commit()
	stmt, err := tx.Prepare(sqlStr)
	if err != nil {
		return -1, err
	}
	defer stmt.Close()
	affectCount := int64(0)
	for _, param := range params {
		res, err := stmt.Exec(param...)
		if err != nil {
			tx.Rollback()
			return -1, err
		}
		tempCount, _ := res.RowsAffected()
		affectCount = affectCount + tempCount
		fmt.Sprintf("%v", res)
	}
	return affectCount, nil
}
